package com.tvunetworks.center.user.util;

import com.tvunetworks.center.user.model.json.ExcelData;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.util.CollectionUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileOutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;


/**
 * @program: tvucc-aggregator
 * @description: ExcelUtil
 * @author chrishuang
 * @create: 2019-06-20 4:02 PM
 **/
@Slf4j
public class ExcelUtil {
    /**
     *
     * @param response response
     * @param fileName fileName
     * @param data data
     * @throws Exception 编译
     */
    public static void exportExcel(HttpServletResponse response, String fileName, ExcelData data) throws Exception{
        // 告诉浏览器用什么软件可以打开此文件
        XSSFWorkbook wb = new XSSFWorkbook();
        try {
            response.setHeader("content-Type", "application/vnd.ms-excel");
            // 下载文件的默认名称
            fileName = fileName.concat(".xlsx");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8"));

            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);
            wb.write(response.getOutputStream());
        } catch (Exception e) {
            log.error("Export excel fail : ",e.getMessage());
            throw new RuntimeException();
        } finally {

            wb.close();
        }
    }

    /**
     *
     * @param fileName fileName
     * @param data data
     * @throws Exception Exception
     */
    public static void exportExcelToFile(String fileName, ExcelData data) throws Exception{
        // 告诉浏览器用什么软件可以打开此文件
        XSSFWorkbook wb = new XSSFWorkbook();
        FileOutputStream fileOut=null;
        try {
            // 下载文件的默认名称
            fileName = fileName.concat(".xlsx");

            String sheetName = data.getName();
            if (null == sheetName) {
                sheetName = "Sheet1";
            }
            XSSFSheet sheet = wb.createSheet(sheetName);
            writeExcel(wb, sheet, data);

            File exportFile = new File(fileName);
            if (!exportFile.exists()) {
                boolean newFile = exportFile.createNewFile();
                System.out.println(newFile);
            }

            fileOut = new FileOutputStream(fileName);
            wb.write(fileOut);
        } catch (Exception e) {
            log.error("Export excel fail : ",e.getMessage());
            throw new RuntimeException();
        } finally {
            if (fileOut != null) {
                fileOut.close();
            }
            wb.close();
        }
    }

    private static void writeExcel(XSSFWorkbook wb, Sheet sheet, ExcelData data) throws Exception {

        //样式
        XSSFCellStyle titleStyle = wb.createCellStyle();
        Font titleFont = wb.createFont();
        titleFont.setBold(true);
        titleFont.setFontHeightInPoints((short) 12);
        titleStyle.setFont(titleFont);
        setBorder(titleStyle, BorderStyle.THIN);
        //写入标题
        int colIndex = 0;
        Row titleRow = sheet.createRow(0);
        for (String field : data.getTitles()) {
            Cell cell = titleRow.createCell(colIndex);
            cell.setCellValue(field);
            cell.setCellStyle(titleStyle);
            colIndex++;
        }

        //写入内容
        XSSFCellStyle contentStyle = wb.createCellStyle();
        setBorder(contentStyle, BorderStyle.THIN);

        List list = data.getContent();
        if (!CollectionUtils.isEmpty(list)) {
            Object t = data.getContent().get(0);
            Field[] fields = t.getClass().getDeclaredFields();
            List<Field> fieldList = Arrays.asList(fields);
            List<String> fieldNameList = new ArrayList<>();
            for (Field field : fieldList) {
                fieldNameList.add(field.getName());
            }
            // 去除忽略的属性
            String[] ignoreList = data.getIgnoreList();
            if (ignoreList != null && ignoreList.length > 0) {
                List<String> ignore = Arrays.asList(ignoreList);
                fieldNameList.removeAll(ignore);
            }

            Class clazz = t.getClass();
            int rowIndex = 1;
            for (Object ts : data.getContent()) {
                Row titleRows = sheet.createRow(rowIndex);
                int columnIndex = 0;
                for (String fieldName : fieldNameList) {
                    Cell cell = titleRows.createCell(columnIndex);
                    String name = "get" + fieldName.substring(0, 1).toUpperCase() + fieldName.substring(1);
                    Method method = clazz.getMethod(name);
                    Object object = method.invoke(ts);
                    cell.setCellStyle(contentStyle);
                    if (object != null) {
                        cell.setCellValue(object.toString());
                    } else {
                        cell.setCellValue("");
                    }
                    columnIndex++;
                }
                rowIndex++;
            }
        }
        //自动调整列宽
        autoSizeColumns(sheet, data.getTitles().length + 1);
    }

    private static void autoSizeColumns(Sheet sheet, int columnNumber) {
        for (int i = 0; i < columnNumber; i++) {
            int orgWidth = sheet.getColumnWidth(i);
            sheet.autoSizeColumn(i, true);
            int newWidth = sheet.getColumnWidth(i) + 100;
            if (newWidth > orgWidth) {
                sheet.setColumnWidth(i, newWidth);
            } else {
                sheet.setColumnWidth(i, orgWidth);
            }
        }
    }

    private static void setBorder(XSSFCellStyle style, BorderStyle border) {
        style.setBorderTop(border);
        style.setBorderLeft(border);
        style.setBorderRight(border);
        style.setBorderBottom(border);
    }
}
